﻿CREATE PROCEDURE [dbo].[UpdateUserProjectMembership]
@UserId INT, @UserMembershipXML TEXT
AS
IF @UserMembershipXML IS NOT NULL
BEGIN
	DECLARE @Membership TABLE(id INT, startDate DATETIME, endDate DATETIME, hourlyRate MONEY)

	 
	DECLARE @idoc INT
	
	 
	EXEC sp_xml_preparedocument @idoc OUTPUT, @UserMembershipXML 
	
	 
	INSERT INTO @Membership (
					id,
					startDate,
					endDate,
					hourlyRate
				)
	SELECT			id,
					startDate,
					endDate,
					hourlyRate
	FROM OPENXML(@idoc, '/projects/project',1)
	WITH(id INT, startDate DATETIME, endDate DATETIME, hourlyRate MONEY)
	
	 
	DELETE FROM ProjectMembers WHERE UserId = @UserId AND ProjectId NOT IN (SELECT id FROM @Membership)
	
	DECLARE @Id INT, @StartDate DATETIME, @EndDate DATETIME, @HourlyRate MONEY
	DECLARE projcursor CURSOR
		FOR SELECT id, startDate, endDate, hourlyRate FROM @Membership
	OPEN projcursor
	FETCH NEXT FROM projcursor 
		INTO @Id, @StartDate, @EndDate, @HourlyRate 
	WHILE @@FETCH_STATUS = 0
	BEGIN
	
		IF EXISTS(SELECT * FROM ProjectMembers WHERE ProjectId = @Id AND UserId = @UserId)
		BEGIN
			UPDATE ProjectMembers
			SET StartDate = @StartDate,
				EndDate = @EndDate,
				HourlyRate = @HourlyRate
			WHERE ProjectId = @Id AND UserId = @UserId				
		END
		ELSE
		BEGIN
			INSERT INTO ProjectMembers(
				ProjectId,
				UserId,
				StartDate,
				EndDate,
				HourlyRate
			)VALUES(
				@Id,
				@UserId,
				@StartDate,
				@EndDate,
				@HourlyRate
			)
		END
		FETCH NEXT FROM projcursor 
		INTO @Id, @StartDate, @EndDate, @HourlyRate 
	END
	CLOSE projcursor
	DEALLOCATE projcursor

END

